package com.example.cv.mapper;

import com.example.cv.model.ACEmployee;
import com.example.cv.model.Candidate;
import com.example.cv.model.Employee;
import com.example.cv.model.Employer;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * @author 周杰
 */
@Mapper
public interface EmployeeMapper {

    /**
     * description:求职者投递简历（插入雇佣表）
     * @param c_id,e_id
     * @return boolean
     */
    @Insert("insert into tb_employee(c_id,e_id,cv_isAgree,interview_isAgree) values(#{c_id},#{e_id},0,0)")
    boolean addEmployee(int c_id, int e_id);

    /**
     * description:求职者查询所有求职消息
     * @param
     * @return java.util.List<com.example.cv.model.Employer>
     */
    @Select("select * from tb_employer where e_id in (select e_id from tb_employee where c_id=#{c_id})")
    List<Employer> findEmployersInEmployeesByCId(int c_id);

    /**
     * description:根据求职者id和用人单位id查询雇佣表的雇员信息
     * @param c_id,e_id
     * @return com.example.cv.model.Employee
     */
    @Select("select * from tb_employee where c_id=#{c_id} and e_id=#{e_id}")
    Employee findEmployeesByIds(int c_id, int e_id);

    /**
     * description:用人单位处理简历信息
     * @param c_id,e_id
     * @return boolean
     */
    @Update("update tb_employee set cv_isAgree=#{cv_isAgree} where c_id=#{c_id} and e_id=#{e_id}")
    boolean informCV(int c_id, int e_id,int cv_isAgree);

    /**
     * description:用人单位根据e_id查询所有投递的个人信息
     * @param e_id
     * @return java.util.List<com.example.cv.model.Candidate>
     */
    @Select("select * from tb_candidate where c_id in (select c_id from tb_employee where e_id=#{e_id})")
    List<Candidate> findCandidatesInEmployeesByEId(int e_id);

    /**
     * description:用人单位处理面试信息
     * @param c_id,e_id
     * @return boolean
     */
    @Update("update tb_employee set interview_isAgree=#{interview_isAgree} where c_id=#{c_id} and e_id=#{e_id}")
    boolean informInterview(int c_id, int e_id, int interview_isAgree);

    /**
     * description:查询所有候选者
     * @param e_id
     * @return java.util.List<com.example.cv.model.Employee>
     */
    @Select("select * from tb_employee where e_id=#{e_id}")
    List<Employee> findEmployeesByEId(int e_id);

    /**
     * description:根据c_id查询候选者
     * @param c_id
     * @return java.util.List<com.example.cv.model.Employee>
     */
    @Select("select * from tb_employee where c_id=#{c_id}")
    List<Employee> findEmployeesByCId(int c_id);

    /**
     * description:根据e_id查找所有已录取的求职者
     * @param e_id
     * @return java.util.List<com.example.cv.model.Candidate>
     */
    @Select("select * from tb_candidate where c_id in (select c_id from tb_employee where e_id=#{e_id} and cv_isAgree=1 and interview_isAgree=1)")
    List<Candidate> findAllAcceptedCandidatesByEId(int e_id);

    @SelectProvider(type=EmployeeMapperProvider.class,method = "findAcceptedCandidatesByParams")
    List<ACEmployee> findAcceptedCandidatesByParams(String school,String e_name);

    class EmployeeMapperProvider{
        public String findAcceptedCandidatesByParams(String school,String e_name){
            return new SQL(){
                {
                    SELECT("*");
                    FROM("tb_ac_employee");
                    WHERE("e_name='"+e_name+"'");
                    if(school!=""){
                        AND();
                        WHERE("school='"+school+"'");
                    }
                    ORDER_BY("CONVERT(major USING gbk)");
                }
            }.toString();
        }
    }

    /**
     * description:每次用人单位通过了面试后则加入录取表中方便后续查询导出
     * @param c_id,e_id
     * @return boolean
     */
    @Insert("INSERT INTO tb_ac_employee(school,c_name,major,e_name) SELECT school,tb_candidate.name as 'c_name',major,tb_employer.name as 'e_name' FROM tb_candidate,tb_employer where c_id=#{c_id} and e_id=#{e_id}")
    boolean addACEmployeeByIds(int c_id, int e_id);
}
